package com.gxuwz.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import com.gxuwz.medical.database.DbUtil;
import com.gxuwz.medical.domain.user.User;
import com.gxuwz.medical.tools.PageBean;
import com.gxuwz.medical.tools.StringUtil;

/**
 * 
 * @ClassName: UserDao
 * @Description: 用户DAO
 * @author 陈震洺
 * @date 2019年3月14日 下午3:42:07
 *
 */
public class UserDao {
	DbUtil dbUtil = new DbUtil(); // 数据库工具类

	/**
	 * 
	 * @Title: login
	 * @Description: 用户登录验证 @author 陈震洺 @param user @return @throws
	 *               Exception @throws
	 */
	public User login(User user) throws Exception {
		Connection con = null;
		con = dbUtil.getCon();
		User result = null;
		String sql = "select * from user where user_id=? and pwd=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, user.getUserid());
		pstmt.setString(2, user.getPwd());
		ResultSet rs = pstmt.executeQuery();

		if (rs.next()) {
			result = new User();
			result.setUserid(user.getUserid());
			result.setPwd(user.getUserid());
		}
		dbUtil.closeCon(con); // 关闭数据库连接
		return result;
	}

	/**
	 * 
	 * @Title: roleList @Description: 用户查询 @author 陈震洺 @param con @param
	 *         pageBean @param role @return @throws Exception @throws
	 */
	public ResultSet userList(PageBean pageBean, User user) throws Exception {
		Connection con = null;
		con = dbUtil.getCon();
		StringBuffer sb = new StringBuffer("select * from user");
		// grade的班级名不为空-->搜索操作
		if (user != null && !StringUtil.isEmpty(user.getUserid())) {
			sb.append(" and user_id like '%" + user.getUserid() + "%'"); //
		} else if (user != null && !StringUtil.isEmpty(user.getFullname())) {
			sb.append(" and fullname like '%" + user.getFullname() + "%'"); //
		}
		if (pageBean != null) {
			sb.append(" limit " + pageBean.getStart() + "," + pageBean.getRows());
		}

		// 搜索操作-->把and 替换成where，不是搜索自然不会替换
		PreparedStatement pstmt = con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		System.out.println(pstmt.executeQuery());
		return pstmt.executeQuery();
	}

	public int userCount(User user) throws Exception {
		Connection con = null;
		con = dbUtil.getCon();
		StringBuffer sb = new StringBuffer("select count(*) as total from user");

		if (!StringUtil.isEmpty(user.getUserid())) {
			sb.append(" and user_id like '%" + user.getUserid() + "%'"); //
		} else if (!StringUtil.isEmpty(user.getFullname())) {
			sb.append(" and fullname like '%" + user.getFullname() + "%'"); //
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString().replaceFirst("and", "where"));
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return rs.getInt("total");

		} else {
			return 0;
		}

	}

	/**
	 * 
	 * @Title: userDelete @Description: 用户删除 @author 陈震洺 @param con @param
	 *         delIds @return @throws Exception @throws
	 */
	public int userDelete(String delIds) throws Exception {
		Connection con = null;
		con = dbUtil.getCon();
		String sql = "delete from user where id in(" + delIds + ")";// in效率高

		PreparedStatement pstmt = con.prepareStatement(sql);
		return pstmt.executeUpdate();
	}

	/**
	 * 
	 * @Title: roleAdd @Description: 用户添加 @author 陈震洺 @param con @param
	 *         role @return @throws Exception @throws
	 */
	public int userAdd(User user) throws Exception {
		Connection con = null;
		con = dbUtil.getCon();
		String sql = "insert user values(null,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, user.getUserid());
		pstmt.setString(2, user.getPwd());
		pstmt.setString(3, user.getFullname());
		return pstmt.executeUpdate();
	}

	/**
	 * 
	 * @Title: roleModify @Description: 修改用户 @author 陈震洺 @param con @param
	 *         role @return @throws Exception @throws
	 */
	public int userModify(User user) throws Exception {
		Connection con = null;
		con = dbUtil.getCon();
		String sql = "update user set user_id=?,fullname=? where id=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, user.getUserid());
		pstmt.setString(2, user.getFullname());
		pstmt.setInt(3, user.getId());
		return pstmt.executeUpdate();
	}
}
